﻿Namespace Functions
    Public Class MetisInternalFunction
        Inherits MetisFunction

        Private _FlatTreeOf As MetisFunction

        Public Shadows Event Saved(ByVal sender As IMetisEntity)
        Public Shadows Event PropertyChanged(ByVal sender As Object, ByVal e As System.ComponentModel.PropertyChangedEventArgs)

        Sub New(ByVal MetisCore As MetisCore, Optional ByVal [Namespace] As MetisNamespace = Nothing)
            _MetisCore = MetisCore
            _Namespace = [Namespace]
            _UsedByFunctions = New MetisFunctionList(_MetisCore, Me)
            _UsesFunctions = New MetisFunctionList(_MetisCore, Me)
            _ID = _MetisCore.GetTemporaryID
            _Name = String.Format(My.Resources.Metis_Dim_NewName.ToString, _ID * -1)
            _Table = New DB.DBTable(_MetisCore, "", DB.DBTableType.Table)
            _IsModified = True
        End Sub

        Sub New(ByVal MetisCore As MetisCore, ByVal [Namespace] As MetisNamespace, ByVal ID As Integer, ByVal Name As String, ByVal Annotation As String, _
                ByVal Table As DB.DBTable, ByVal ModifiedOn As Date, ByVal CreatedOn As Date)

            _MetisCore = MetisCore
            _Namespace = [Namespace]
            _UsedByFunctions = New MetisFunctionList(_MetisCore, Me)
            _UsesFunctions = New MetisFunctionList(_MetisCore, Me)
            _ID = ID
            _Name = Name
            _BackupName = Name
            _Annotation = Annotation
            _BackupAnnotation = Annotation
            _Table = Table
            _ModifiedOn = ModifiedOn
            _CreatedOn = CreatedOn
            _Level = 0
            _LoadState = MetisLoadState.NotLoaded
        End Sub

        Public Overrides Sub Reload()
            If _LoadState <> MetisLoadState.NewDefinition Then
                Dim OraReader As OracleDataReader
                Dim OraCmd As OracleCommand

                _LoadState = MetisLoadState.Loading

                'Dimension base information
                OraCmd = New OracleCommand(String.Format("SELECT name, table_name, description, modified_on, created_on " & _
                                                         "FROM dam_functions where function_id={0}", _ID), _MetisCore.Connection)
                OraReader = OraCmd.ExecuteReader
                If OraReader.Read Then
                    _Name = OraReader.GetString(0)
                    _Table = _MetisCore.DBTables.FindTable(OraReader.GetString(1))
                    If _Table Is Nothing Then _Table = New DB.DBTable(_MetisCore, OraReader.GetString(1), DB.DBTableType.Table)

                    _Annotation = If(OraReader.IsDBNull(2), "", OraReader.GetString(2))

                    _ModifiedOn = OraReader.GetDateTime(3)
                    _CreatedOn = OraReader.GetDateTime(4)

                    _BackupName = _Name
                    _Annotation = _BackupAnnotation

                    OraReader.Close()

                    'Fields
                    OraCmd = New OracleCommand(String.Format("SELECT function_field_id, field_name, field_type, db_field_name, expression, description, " & _
                                                             "dimension_fk, parent_field, modified_on, created_on, number_format, NVL(column_width,64) " & _
                                                             "FROM dam_function_fields WHERE function_fk={0} ORDER BY seq", _ID), _MetisCore.Connection)
                    OraReader = OraCmd.ExecuteReader
                    _Fields.Clear()
                    _BackupFields.Clear()
                    Do While OraReader.Read

                        Dim tmpFieldExpr As String = If(OraReader.IsDBNull(4), "", OraReader.GetString(4))
                        Dim tmpFieldAnnot As String = If(OraReader.IsDBNull(5), "", OraReader.GetString(5))
                        Dim tmpFieldDim As Dimensions.MetisDimension = If(OraReader.IsDBNull(6), Nothing, _MetisCore.Dimensions.Find(OraReader.GetInt32(6)))
                        Dim tmpFieldParent As Integer = If(OraReader.IsDBNull(7), -1, OraReader.GetInt32(7))
                        Dim tmpFieldFormat As String = If(OraReader.IsDBNull(10), "", OraReader.GetString(10))

                        Dim tmpField As New MetisFunctionField(Me, OraReader.GetInt32(0), OraReader.GetString(1), OraReader.GetInt32(2), _
                                                                  OraReader.GetString(3), tmpFieldExpr, tmpFieldAnnot, tmpFieldDim, tmpFieldParent, _
                                                                  OraReader.GetDateTime(8), OraReader.GetDateTime(9), _
                                                                  tmpFieldFormat, OraReader.GetInt32(11))
                        _Fields.Add(tmpField)
                        _BackupFields.Add(tmpField.Clone)
                    Loop

                    'Uses
                    'OraCmd = New OracleCommand(String.Format("SELECT DISTINCT uses_function_fk FROM dam_function_ref " & _
                    '                                         "WHERE function_fk={0}", _ID), _MetisCore.Connection)
                    'OraReader = OraCmd.ExecuteReader
                    '_UsesFunctions.Clear()
                    'Do While OraReader.Read
                    '    Dim tmpFunc As Core.Functions.MetisFunction = _MetisCore.Functions.Find(OraReader.GetInt32(0))
                    '    If tmpFunc IsNot Nothing Then _UsesFunctions.Add(tmpFunc)
                    'Loop

                    'UsedBy
                    OraCmd = New OracleCommand(String.Format("SELECT DISTINCT function_fk FROM dam_function_ref " & _
                                                             "WHERE uses_function_fk={0}", _ID), _MetisCore.Connection)
                    OraReader = OraCmd.ExecuteReader
                    _UsedByFunctions.Clear()
                    Do While OraReader.Read
                        Dim tmpFunc As Core.Functions.MetisFunction = _MetisCore.Functions.Find(OraReader.GetInt32(0))
                        If tmpFunc IsNot Nothing Then _UsedByFunctions.Add(tmpFunc)
                    Loop

                End If
                OraReader.Close()

                _LoadState = MetisLoadState.Loaded
                _IsModified = False
                _LastUpdate = Date.Now
                'RaiseEvent Loaded(Me)
            End If
        End Sub

        Public Overrides Property Table() As DB.DBTable
            Get
                Return _Table
            End Get
            Set(ByVal value As DB.DBTable)
                MyBase.Table = value


                Dim OraReader As OracleDataReader
                Dim OraCmd As OracleCommand

                OraCmd = New OracleCommand(String.Format("SELECT col.column_id, col.column_name, DECODE(pk_col.column_name,NULL,'N','Y') pk, " & _
                                                         "col_com.comments, dim_col.dimension_id dim " & _
                                                         "FROM user_tab_columns col, " & _
                                                         "(SELECT c_col.column_name, c_col.table_name " & _
                                                         " FROM user_cons_columns c_col, user_constraints c_constr " & _
                                                         " WHERE(c_col.constraint_name = c_constr.constraint_name) " & _
                                                         " AND c_constr.table_name='{0}' AND c_constr.constraint_type='P') pk_col, " & _
                                                         "(SELECT * FROM user_col_comments where table_name='{0}') col_com, " & _
                                                         "(SELECT uc.table_name, ucc.column_name, d.dimension_id " & _
                                                         " FROM user_constraints ruc,  user_constraints uc, user_cons_columns ucc, dam_dimensions d " & _
                                                         " WHERE uc.table_name='{0}' AND uc.constraint_type='R' " & _
                                                         " AND uc.constraint_name=ucc.constraint_name " & _
                                                         " AND uc.r_constraint_name=ruc.constraint_name " & _
                                                         " AND ruc.table_name=d.table_name) dim_col " & _
                                                         "WHERE col.table_name=pk_col.table_name(+) AND col.column_name=pk_col.column_name(+) " & _
                                                         "AND col.table_name=col_com.table_name(+) AND col.column_name=col_com.column_name(+) " & _
                                                         "AND col.table_name=dim_col.table_name(+) AND col.column_name=dim_col.column_name(+) " & _
                                                         "AND col.table_name='{0}' ORDER BY col.column_id", value.TableName), _MetisCore.Connection)

                OraReader = OraCmd.ExecuteReader
                _Fields.Clear()
                Do While OraReader.Read
                    Dim tmpField As New MetisFunctionField(Me)
                    tmpField.Name = OraReader.GetString(1)
                    tmpField.DBFieldName = OraReader.GetString(1)
                    If OraReader.GetString(2) = "Y" Then tmpField.FieldType = MetisFunctionFieldType.KeyField
                    Try
                        tmpField.Annotation = OraReader.GetString(3)
                    Catch ex As Exception
                        tmpField.Annotation = ""
                    End Try

                    Try
                        tmpField.DimensionID = OraReader.GetInt32(4)
                    Catch ex As Exception
                        tmpField.Dimension = Nothing
                    End Try

                    _Fields.Add(tmpField)
                Loop
                OraReader.Close()

            End Set
        End Property


        Public Overrides Sub Save()
            Dim OraCmd As OracleCommand

            Dim SQLscript As String = ""
            Dim sSetString As String = ""
            Dim tmpFieldID As Integer
            Dim tmpFieldOrder As Integer = 0

            If _LoadState = MetisLoadState.NewDefinition Then
                _ID = _MetisCore.GetNextID
                If _Namespace IsNot Nothing Then
                    sSetString = _Namespace.ID
                Else
                    sSetString = "NULL"
                End If
                SQLscript &= String.Format("INSERT INTO dam_functions (function_id, name, table_name, func_type, description, namespace_fk) " & _
                                           "VALUES ({0}, '{1}', '{2}', 0, '{3}', {4});", _ID, _Name, _Table.TableName, _Annotation.Replace("'", "''"), sSetString) & vbCrLf
            Else
                If _Name <> _BackupName Or _Annotation <> _BackupAnnotation Then
                    sSetString = ""
                    If _Name <> _BackupName Then sSetString &= ",name='" & _Name & "'"
                    If _Annotation <> _BackupAnnotation Then sSetString &= ",description='" & _Annotation.Replace("'", "''") & "'"
                    SQLscript &= String.Format("UPDATE dam_functions SET {1} " & "WHERE function_id={0};", _ID, sSetString.Substring(1)) & vbCrLf
                End If
                SQLscript &= String.Format("DELETE FROM dam_function_fields WHERE function_fk={0};", _ID) & vbCrLf
            End If

            For Each item As MetisFunctionField In _Fields
                tmpFieldID = _MetisCore.GetNextFieldID
                If item.Dimension IsNot Nothing Then
                    sSetString = item.DimensionID
                Else
                    sSetString = "NULL"
                End If
                SQLscript &= String.Format("INSERT INTO dam_function_fields (function_field_id, field_name, db_field_name, function_fk, seq, field_type, dimension_fk) " & _
                                           "VALUES ({0}, '{1}', '{2}', {3}, {4}, {5}, {6});", tmpFieldID, item.Name, item.DBFieldName, _ID, tmpFieldOrder, _
                                           IIf(item.FieldType = MetisFunctionFieldType.KeyField, 1, 0), sSetString) & vbCrLf
                tmpFieldOrder += 1
            Next

            If SQLscript <> "" Then
                SQLscript = String.Format("--- Save {1} ---{2}BEGIN{2}{0} COMMIT;{2}END;{2}", SQLscript, _Name, vbCrLf)
                OraCmd = New OracleCommand(SQLscript, _MetisCore.Connection)
                OraCmd.ExecuteNonQuery()
            End If

            RaiseEvent Saved(Me)
            _LoadState = MetisLoadState.NotLoaded
            Reload()
            If _MetisCore.Functions.Find(_ID) Is Nothing Then
                _MetisCore.Functions.Add(Me)
            End If
            RaiseEvent PropertyChanged(Me, New System.ComponentModel.PropertyChangedEventArgs("Name"))
        End Sub

        Public Overrides ReadOnly Property FunctionType() As MetisFunctionType
            Get
                Return MetisFunctionType.Internal
            End Get
        End Property

        Public Overrides Sub CheckIsModified()

        End Sub
    End Class
End Namespace